package domain.web.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.NumberFormat;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

import javax.annotation.PostConstruct;
import javax.sql.DataSource;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.PreparedStatementCreator;
import org.springframework.jdbc.core.support.JdbcDaoSupport;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;
import org.springframework.stereotype.Repository;

import domain.web.dao.ProductDao;
import domain.web.model.Product;
import domain.web.model.ProductView;

@Repository("productDao")
public class ProductDaoImpl extends JdbcDaoSupport implements ProductDao {

	@Autowired
	private DataSource dataSource;

	private NumberFormat fm;

	@PostConstruct
	void init() {
		setDataSource(dataSource);
		fm = NumberFormat.getNumberInstance();
	}

	@Override
	public long insert(final Product product) {
		final KeyHolder keyHolder = new GeneratedKeyHolder();
		final PreparedStatementCreator psc = new PreparedStatementCreator() {
			public PreparedStatement createPreparedStatement(Connection con)
					throws SQLException {
				PreparedStatement pstmt = con.prepareStatement(
						"INSERT INTO Products(CategoryId, ManufacturerId, Name, Uri, Dcrp, ImageUrl) "
								+ "VALUES(?,?,?,?,?,?)",
						Statement.RETURN_GENERATED_KEYS);
				pstmt.setLong(1, product.getCategoryId());
				pstmt.setLong(2, product.getManufacturerId());
				pstmt.setString(3, product.getName());
				pstmt.setString(4, product.getUri());
				pstmt.setString(5, product.getDcrp());
				pstmt.setString(6, product.getImageUrl());
				return pstmt;
			}
		};
		getJdbcTemplate().update(psc, keyHolder);
		return keyHolder.getKey().longValue();
	}

	@Override
	public void update(Product product) {
		String sql = "UPDATE Products SET CategoryId=?, ManufacturerId=?, Name=?, Uri=?, Dcrp=?, ImageUrl=? "
				+ "WHERE Id = ?";
		getJdbcTemplate().update(sql, product.getCategoryId(),
				product.getManufacturerId(), product.getName(),
				product.getUri(), product.getDcrp(), product.getImageUrl(),
				product.getId());
	}

	@Override
	public void delete(long id) {
		String sql = "DELETE FROM Products WHERE Id = ?";
		getJdbcTemplate().update(sql, id);
	}

	@Override
	public Product get(String uri) {
		String sql = "SELECT * FROM Products WHERE Uri = ?";
		List<Map<String, Object>> results = getJdbcTemplate().queryForList(sql,
				uri);
		return results.isEmpty() ? null : rowMapper(results.get(0));
	}

	@Override
	public ProductView getView(long id, String locale) {
		String sql = "SELECT ProductId, CategoryName_"
				+ locale
				+ " AS Category, ManufacturerName, ProductName, Dcrp, ImageUrl, MinPrice, StoreCount "
				+ "FROM ProductsView WHERE ProductId = ?";
		List<Map<String, Object>> results = getJdbcTemplate().queryForList(sql,
				id);
		return results.isEmpty() ? null : rowMapperView(results.get(0));
	}

	@Override
	public List<ProductView> getViewList(String locale) {
		String sql = "SELECT ProductId, CategoryName_"
				+ locale
				+ " AS Category, ManufacturerName, ProductName, Dcrp, ImageUrl, MinPrice, StoreCount FROM ProductsView";
		List<Map<String, Object>> results = getJdbcTemplate().queryForList(sql);
		List<ProductView> products = new ArrayList<ProductView>();
		for (Map<String, Object> map : results) {
			products.add(rowMapperView(map));
		}
		return products;
	}

	@Override
	public List<ProductView> getViewListByUri(String criteria, Double price,
			String manufactorer, String locale) {
		String sql = "SELECT ProductId, CategoryName_"
				+ locale
				+ " AS Category, ManufacturerName, ProductName, Dcrp, ImageUrl, MinPrice, StoreCount FROM ProductsView Where ProductUri IN ("
				+ criteria + ")";
		if (manufactorer != null && !manufactorer.isEmpty()) {
			sql += " AND ManufacturerId IN(" + manufactorer + ")";
		}

		if (price != null && price > 0) {
			sql += " AND MinPrice < " + price;
		}

		List<Map<String, Object>> results = getJdbcTemplate().queryForList(sql);
		List<ProductView> products = new ArrayList<ProductView>();
		for (Map<String, Object> map : results) {
			products.add(rowMapperView(map));
		}

		System.out.println(sql);

		return products;
	}

	private Product rowMapper(Map<String, Object> row) {
		Product product = new Product();
		product.setId((Long) row.get("Id"));
		product.setCategoryId((Long) row.get("CategoryId"));
		product.setManufacturerId((Long) row.get("ManufacturerId"));
		product.setName((String) row.get("Name"));
		product.setUri((String) row.get("Uri"));
		product.setDcrp((String) row.get("Dcrp"));
		product.setImageUrl((String) row.get("ImageUrl"));
		return product;
	}

	private ProductView rowMapperView(Map<String, Object> row) {
		ProductView product = new ProductView();
		product.setId((Long) row.get("ProductId"));
		product.setCategory((String) row.get("Category"));
		product.setManufacturer((String) row.get("ManufacturerName"));
		product.setName((String) row.get("ProductName"));
		product.setDcrp((String) row.get("Dcrp"));
		product.setImageUrl((String) row.get("ImageUrl"));
		product.setStoreCount((Long) row.get("StoreCount"));

		Long minPrice = (Long) row.get("MinPrice");
		product.setMinPrice(fm.format(minPrice) + " VND");
		return product;
	}
}
